import sqlite3
import os
import json
from config import DATABASE_PATH

# 数据库文件路径
DB_PATH = DATABASE_PATH

def init_db():
    """初始化数据库"""
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    
    # 创建视频记录表
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS video_records (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        original TEXT NOT NULL,
        preview TEXT NOT NULL,
        duration REAL,
        time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        is_favorite BOOLEAN DEFAULT 0
    )
    """)
    
    conn.commit()
    conn.close()

def save_records(video_path, preview_paths, duration=None):
    """保存视频处理记录到数据库"""
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    
    # 将preview_paths列表转换为JSON字符串
    preview_json = json.dumps(preview_paths)
    
    # 插入记录
    cursor.execute("""
    INSERT INTO video_records (original, preview, duration, is_favorite)
    VALUES (?, ?, ?, 0)
    """, (video_path, preview_json, duration))
    
    conn.commit()
    conn.close()

def update_record_favorite(record_id, is_favorite):
    """更新视频收藏状态"""
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    
    cursor.execute("""
    UPDATE video_records
    SET is_favorite = ?
    WHERE id = ?
    """, (1 if is_favorite else 0, record_id))
    
    conn.commit()
    conn.close()

def get_all_records():
    """获取所有视频记录"""
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    cursor.execute("""
    SELECT * FROM video_records
    ORDER BY id ASC
    """)
    
    records = []
    for row in cursor.fetchall():
        record = dict(row)
        # 将JSON字符串转换回列表
        try:
            record['preview'] = json.loads(record['preview'])
        except:
            record['preview'] = [record['preview']]  # 兼容旧数据
        records.append(record)
    
    conn.close()
    return records

def get_record_by_id(record_id):
    """根据ID获取单个记录"""
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    cursor.execute("""
    SELECT * FROM video_records
    WHERE id = ?
    """, (record_id,))
    
    record = cursor.fetchone()
    if record:
        record = dict(record)
        # 将JSON字符串转换回列表
        try:
            record['preview'] = json.loads(record['preview'])
        except:
            record['preview'] = [record['preview']]  # 兼容旧数据
    
    conn.close()
    return record

def delete_record(record_id):
    """删除记录"""
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    
    cursor.execute("""
    DELETE FROM video_records
    WHERE id = ?
    """, (record_id,))
    
    conn.commit()
    conn.close()

def get_all_processed_videos():
    """获取所有已处理的视频路径列表"""
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    
    cursor.execute("""
    SELECT original FROM video_records
    """)
    
    videos = [row[0] for row in cursor.fetchall()]
    conn.close()
    return videos

def update_record_path(record_id, new_path):
    """更新视频文件路径"""
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    
    cursor.execute("""
    UPDATE video_records
    SET original = ?
    WHERE id = ?
    """, (new_path, record_id))
    
    conn.commit()
    conn.close()
    